﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.OleDb;
using System.Configuration;

/// <summary>
/// Summary description for Vinarii_servis
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Vinarii_servis : System.Web.Services.WebService
{

    public Vinarii_servis()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public List<Vinarii> prikaziVinarii(int idGrad)
    {
        List<Vinarii> lista = new List<Vinarii>();

        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();

        string select = "SELECT Ime, Adresa, email, telefon, webstrana FROM Vinarija WHERE grad = ?";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", idGrad);

        OleDbDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Vinarii v = new Vinarii(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), idGrad, reader[4].ToString());
            lista.Add(v);
        }

        conn.Close();
        return lista;
    }

    [WebMethod]
    public List<Vina> prikaziVina(int idVinarija)
    {
        List<Vina> lista = new List<Vina>();

        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();
        int id = idVinarija;
        string select = "SELECT ImeVino,  Sorta, SortaNaGrozje, Alkohol FROM Vina WHERE IDVinarija = ?";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", id);

        OleDbDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Vina v = new Vina(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString());
            lista.Add(v);
        }

        conn.Close();
        return lista;

    }

    [WebMethod]
    public int getIDVinarija(string ime)
    {
        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();

        string select = "SELECT IDVinarija FROM Vinarija WHERE Ime = ?";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", ime);

        int id = Convert.ToInt32(cmd.ExecuteScalar());
        conn.Close();
        return id;


    }

    [WebMethod]
    public Vinarii infoVinarija(string ime)
    {

        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();
        // int idv = id;
        string select = "SELECT  Adresa, email, telefon, grad, webstrana FROM Vinarija WHERE Ime = ? ";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", ime);
        OleDbDataReader r = cmd.ExecuteReader();
        r.Read();
        Vinarii v = new Vinarii(ime, r[0].ToString(), r[1].ToString(), r[2].ToString(), Convert.ToInt32(r[3].ToString()), r[4].ToString());

        conn.Close();
        return v;
    }

    [WebMethod]
    public Vina infoVino(int idVino)
    {
        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();

        string select = "SELECT Sorta, SortaNaGrozje, Alkohol FROM Vina WHERE IDVino = ?";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", idVino);
        OleDbDataReader r = cmd.ExecuteReader();
        r.Read();
        Vina vino = new Vina("",r[0].ToString(), r[1].ToString(), r[2].ToString());

        conn.Close();
        return vino;
    }

    [WebMethod]
    public int getIDVino(string imeVino, int idvinarija)
    {
        string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
        OleDbConnection conn = new OleDbConnection(conString);

        conn.Open();
        string select = "SELECT IDVino FROM Vina WHERE IDVinarija = ? AND ImeVino = ? ";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.Parameters.AddWithValue("?", idvinarija);
        cmd.Parameters.AddWithValue("?", imeVino);
        int idVino = Convert.ToInt32(cmd.ExecuteScalar());

        conn.Close();
        return idVino;
    }

    /*   [WebMethod]
       public int getCenaZaVino(int vino)
       {
           string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
           OleDbConnection conn = new OleDbConnection(conString);

           conn.Open();
           string select = "SELECT CenaZaLitar FROM Vina WHERE IDVino = ? ";
           OleDbCommand cmd = new OleDbCommand(select, conn);
           cmd.Parameters.AddWithValue("?", vino);
           int cena=  Convert.ToInt32(cmd.ExecuteScalar());
           conn.Close();
           return cena;
       }*/
}
public class Vina
{
  public  string Ime;
  
  public  string sortaGrozje;
  public  string sorta;
   public string alkohol;

    public Vina(string Ime,  string sorta, string sortaGrozje, string alkohol)
    {
        this.Ime = Ime;
        
        this.sortaGrozje = sortaGrozje;
        this.sorta = sorta;
        this.alkohol = alkohol;
    }
}

public class Vinarii{
    public string Ime;
    public string Adresa;
    public string Email;
    public string Telefon;
    public int Grad;
    public string Web;

    public Vinarii(string Ime, string Adresa, string Email, string Telefon, int Grad, string Web){
        this.Ime=Ime;
        this.Adresa=Adresa;
        this.Email=Email;
        this.Telefon=Telefon;
        this.Grad=Grad;
        this.Web=Web;
    }


}

